package com.cpp.db;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;


/**
 * 数据库工具函数类。实现一个简单的连接池。 
 * @author 
 */
public class DBUtils {
	// 连接池，key是连接池名字，value是连接池中的连接
	private static List<Connection> pool = new ArrayList<Connection>();
	// 正在使用的连接
	private static HashSet<Connection> usedConns = new HashSet<Connection>();
	// 检查连接是否仍然有效的sql语句
	private static String sql_valid = "select 1";
	
	
	public static List<Connection> getPool(){
		return pool;
	}
	
	public static HashSet<Connection> getUsedConns(){
		return usedConns;
	}
	
	/**
	 * 得到一个数据库连接。
	 * @param statid 统计库id 从dbconfig.xml文件中得到
	 * @param dbid 数据库在统计库中的id
	 * @throws Exception 未找到统计库的信息
	 */
    public synchronized static Connection getConnection() throws Exception {
    	List<Connection> connList = pool;
    	
    	// 从列表中查找出第一个没有被使用并且有效的连接
    	for (int i = 0; i < connList.size(); i++) {
    		Connection conn = connList.get(i);
    		if (usedConns.contains(conn)) {
    			continue;
    		}
    		if (!isValid(conn)) {
    			// 如果连接已经失效，从池中移除
    			try {
    				conn.close();
    			} catch (Exception e) {
    				e.printStackTrace();
    			}
    			connList.remove(i);
    			i--;
    			continue;
    		}
    		usedConns.add(conn);
    		return conn;
    	}
    	
    	// 没有找到符合条件的空闲连接，创建一个新的
        try {
        	String url = "jdbc:mysql://127.0.0.1:3306/chepp?characterEncoding=utf-8";
        	String user = "root";
        	String pass = "kingbird";
        	
        			
			Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, pass);
            connList.add(conn);
            usedConns.add(conn);
            return conn;
		} catch(ClassNotFoundException e) {
			e.printStackTrace();
		} catch(SQLException e1) {
			System.out.println("------------------sql执行异常------------------------------------------");
			System.out.println(e1.toString());
			System.out.println("------------------------------------------------------------");
		}
		return null;
    }
    
    /**
     * 释放一个数据库连接。
     * @param conn
     */
    public synchronized static void releaseConnection(Connection conn) {
    	usedConns.remove(conn);
    }
    
    // 检查一个连接是否仍然有效
    public static boolean isValid(Connection conn) {
    	Statement stmt = null;
    	ResultSet rs = null;
    	try {
    		stmt = conn.createStatement();    		
    		rs = stmt.executeQuery(sql_valid);
    		return true;
    	} catch (Exception e) {
    		return false;
    	} finally {
    		if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
			}
    	}
    }

    /**
     * 执行一个无参数的SQL查询，并返回查询结果。
     * @param pool 连接池ID
     * @param sql SQL语句
     * @throws Exception
     */
	public static List<ResultRow> query(String sql) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			ArrayList<ResultRow> ret = new ArrayList<ResultRow>();
			int count = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				ResultRow row = new ResultRow(count);
				for (int i = 0; i < count; i++) {
					row.setColumn(i + 1, rs.getObject(i + 1));
				}
				ret.add(row);
			}
			return ret;
		} catch (Exception e) {
			System.out.println(sql);
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				releaseConnection(conn);
			}
		}
	}
	
	
	/**
     * 执行一个无参数的只返回整数的SQL查询。
     * @param pool 连接池ID
     * @param sql SQL语句
     * @throws Exception
     */
	public static int queryInt(String sql) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (Exception e) {
			System.out.println(sql);
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				releaseConnection(conn);
			}
		}
	}
	
	/**
     * 执行一个带参数的SQL查询，并返回查询结果。
     * @param pool 连接池ID
     * @param sql SQL语句
     * @param params 查询参数
     * @throws Exception
     */
	public static List<ResultRow> query(String sql, Object[] params) throws Exception {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				if (params[i] instanceof String) {
					stmt.setString(i + 1, (String)params[i]);
				} else if (params[i] instanceof Integer) {
					stmt.setInt(i + 1, ((Integer)params[i]).intValue());
				} else if (params[i] instanceof byte[]) {
					byte[] buf = (byte[])params[i];
					stmt.setBinaryStream(i + 1, new ByteArrayInputStream(buf), buf.length);
				} else if (params[i] instanceof Date) {
					stmt.setDate(i + 1, new java.sql.Date(((Date)params[i]).getTime()));
				}
			}
			rs = stmt.executeQuery();
			ArrayList<ResultRow> ret = new ArrayList<ResultRow>();
			int count = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				ResultRow row = new ResultRow(count);
				for (int i = 0; i < count; i++) {
					row.setColumn(i + 1, rs.getObject(i + 1));
				}
				ret.add(row);
			}
			return ret;
		} catch (Exception e) {
			System.out.println(sql);
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				releaseConnection(conn);
			}
		}
	}
	/**
     * 执行一个带参数的只返回整数的SQL查询。
     * @param pool 连接池ID
     * @param sql SQL语句
     * @param params 查询参数
     * @throws Exception
     */
	public static int queryInt(String sql, Object[] params) throws Exception {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				if (params[i] instanceof String) {
					stmt.setString(i + 1, (String)params[i]);
				} else if (params[i] instanceof Integer) {
					stmt.setInt(i + 1, ((Integer)params[i]).intValue());
				} else if (params[i] instanceof byte[]) {
					byte[] buf = (byte[])params[i];
					stmt.setBinaryStream(i + 1, new ByteArrayInputStream(buf), buf.length);
				} else if (params[i] instanceof Date) {
					stmt.setDate(i + 1, new java.sql.Date(((Date)params[i]).getTime()));
				}
			}
			rs = stmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} catch (Exception e) {
			System.out.println(sql);
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				releaseConnection(conn);
			}
		}
	}
	
	/**
     * 执行一个带参数的只返回整数的SQL查询。
     * @param pool 连接池ID
     * @param sql SQL语句
     * @param params 查询参数
     * @throws Exception
     */
	public static long queryLong( String sql, Object[] params) throws Exception {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				if (params[i] instanceof String) {
					stmt.setString(i + 1, (String)params[i]);
				} else if (params[i] instanceof Integer) {
					stmt.setInt(i + 1, ((Integer)params[i]).intValue());
				} else if (params[i] instanceof byte[]) {
					byte[] buf = (byte[])params[i];
					stmt.setBinaryStream(i + 1, new ByteArrayInputStream(buf), buf.length);
				} else if (params[i] instanceof Date) {
					stmt.setDate(i + 1, new java.sql.Date(((Date)params[i]).getTime()));
				}
			}
			rs = stmt.executeQuery();
			if (rs.next()) {
				return rs.getLong(1);
			}
			return 0;
		} catch (Exception e) {
			System.out.println(sql);
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				releaseConnection(conn);
			}
		}
	}
	
	/**
     * 执行一个返回超大结果集的查询。
     * @param pool 连接池ID
     * @param sql SQL语句
     * @param params 查询参数
     * @return 返回一个结果集枚举器，使用完以后必须调用其close方法释放资源
     * @throws Exception
     */
	public static ResultIterator iterate(String sql, Object[] params) throws Exception {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				if (params[i] instanceof String) {
					stmt.setString(i + 1, (String)params[i]);
				} else if (params[i] instanceof Integer) {
					stmt.setInt(i + 1, ((Integer)params[i]).intValue());
				} else if (params[i] instanceof byte[]) {
					byte[] buf = (byte[])params[i];
					stmt.setBinaryStream(i + 1, new ByteArrayInputStream(buf), buf.length);
				} else if (params[i] instanceof Date) {
					stmt.setDate(i + 1, new java.sql.Date(((Date)params[i]).getTime()));
				}
			}
			rs = stmt.executeQuery();
			return new ResultIterator(rs, stmt, conn);
		} catch (Exception e) {
			System.out.println(sql);
			throw e;
		}
	}
	
	/**
     * 执行一个无参数的SQL更新(delete或update)。
     * @param pool 连接池ID
     * @param sql SQL语句
     * @throws Exception
     */
	public static int update(String sql) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		int result = 0;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			result = stmt.executeUpdate(sql);
		} catch (Exception e) {
			System.out.println(sql);
			throw e;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				releaseConnection(conn);
			}
		}
		return result;
	}
	
	/**
     * 执行一个带参数的SQL更新(delete或update)。
     * @param conn 数据库连接对象 
     * @param pool 连接池ID
     * @param params 查询参数
     * @throws Exception
     */
	public static int update(String sql, Object[] params) throws Exception {
		Connection conn = null;
		int result = 0;
		PreparedStatement stmt = null;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				if (params[i] instanceof String) {
					stmt.setString(i + 1, (String)params[i]);
				} else if (params[i] instanceof Integer) {
					stmt.setInt(i + 1, ((Integer)params[i]).intValue());
				} else if (params[i] instanceof byte[]) {
					byte[] buf = (byte[])params[i];
					stmt.setBinaryStream(i + 1, new ByteArrayInputStream(buf), buf.length);
				} else if (params[i] instanceof Date) {
					stmt.setObject(i + 1, params[i]);
					//stmt.setDate(i + 1, new java.sql.Date(((Date)params[i]).getTime()));
					//stmt.setDate(i + 1, (java.sql.Date)params[i]);
				}
			}
			result = stmt.executeUpdate();
		} catch (Exception e) {
			System.out.println(sql);
			throw e;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				releaseConnection(conn);
			}
		}
		return result;
	}
	
	/**
	 * 关闭所有连接。
	 */
	public synchronized static void clear() {		
		for (Connection c : pool) {
			try {
	            c.close();
	        } catch(SQLException e) {
	        }
		}		
		pool.clear();
	}
	
	
}